2014-12-22 - 10735 - Spec - Contract and Sales Order Creation from Order Sheet with Multiple Tabs - (Dan B by 2015-01-29) #ABAP #JapanABAP #JapanSD
SPECIFICATIONS
10735-Spec-Contract and Sales order creation from Order sheet with Multiple tabs
Purpose
Program to create Contract/ Sales order from order sheet upload with multiple tab.
Admin Info
| Purpose |
Develop a program to create Contract and Sales order from order sheet upload with multiple tab |
| Requested By |
Daniel Brennan |
| Spec Created By |
Siba Mohapatra |
| Spec Created Date |
12/31/2014 |
| Spec QA by |
Surya Basa/Siba Mohapatra |
| Objects |
ZSD_JPN_ORD_SHT_MULTIPLE |
| Document Status |
WIP |
Estimates
| Sl.No |
Activity |
Estimation in Hours |
| 1 |
Research |
|
| 2 |
Documentation |
|
| 3 |
Development/ Config |
|
| 4 |
Unit test in DEV |
|
| 5 |
Unit test in QUA |
|
| 6 |
Other activity |
|
| TOTAL |
References
Prior Tickets
- SD - Siba-Venkat -2014-07-15 - 10735 - Spec - New Era Japan IT Deployment - Contract Creation from Order Sheet
- 2014-11-24 - 10735 - Spec - New Era Japan IT Deployment - Sales Order Creation from Order Sheet
Documents
[Attach any document(s) received for the requirement(s)]
Spec Changes
| Sl. |
Change Details |
Requested By |
Requested On |
Notes if any |
| 1 |
Update Order sheet program to create Contract/Sales order with multiple sheet |
Daniel Brennan |
01/07/2015 |
NA |
| 2 |
Update Order sheet program to handle protected sheet |
Daniel Brennan |
01/07/2015 |
NA |
| 3 |
Update Order sheet program to handle multiple item/size for Contract creation |
Daniel Brennan |
01/07/2015 |
NA |
Functional Requirement
Purpose/WHY:
Sales Process in Japan is little different. The customers are given a brochure and a sell sheet in which the customers choose required materials and quantity and send the sell sheet out as an Order.The sell sheet from the customer should be processed and one contract/sales order should be created per sell sheet.
Sample Load File:
Screen shot of the above Load File:
In Scope:
This program creates contracts and sales order only for Japan by uploading file from desktop.
Out of Scope:
- Non Japan Contracts and sales orders are not considered.
- This program cannot be run in background.
Solution Summary
[Discuss this section with Requester and get approval prior to beginning work]
Develop a Program to create contract/ Sales order from Order Sheet. Order Sheet is an Excel File which is uploaded from desktop and one contract/ sales order is created per SHEET in the load file.
Test Plan
Below are the test scenarios can be tested.| Test ID |
Test Scenarios |
Expected Results |
| 1 |
Fill the order sheet with 3 materials .For item 3 the quantities are not given for the corresponding size and upload into the sell sheet program and execute it. |
Sales Order/Contract should not be created as there are no quantities for the size of item 3. |
| 2 |
Fill the order sheet with 3 materials with two valid (item 1, 2) and one invalid material with quantities (item 3).Upload into the sell sheet program and execute it. |
Sales Order/Contract should be not be created and the appropriate system message should be displayed. |
| 3 |
Upload the order sheet without Ship-to value, into the sell sheet program and execute it. Sheet-1(without Ship-to),sheet-2 (with Ship-to). |
Sales Order/Contract should not be created for Sheet 1. And there sales order/Contract should be created for Sheet2. |
| 4 |
Upload the order sheet without Requested delivery date into the sell sheet program and execute it. |
Sales Order/Contract should not be created. |
| 5 |
Upload the order sheet with invalid month/date into the sell sheet program and execute it. |
Sales Order/Contract should not create and message: ‘Enter a valid Date’should come. |
| 6 |
Upload a protected order sheet, filled with the required data into the sell sheet program then execute it. |
Sales Order/Contract should create accordingly with the data filled in excel file. |
| 7 |
Upload an unprotected order sheet, filled with the required data into the sell sheet program then execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create accordingly with the data filled in excel file. |
| 8 |
Upload an order sheet with .xls format filled with the required data into the sell sheet program and execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create accordingly with the data filled in excel file. |
| 9 |
Upload an order sheet with .xlsx format filled with the required data into the sell sheet program then execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create accordingly with the data filled in excel file. |
| 10 |
Upload an order sheet, filled with future requested delivery date into the sell sheet program then execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create with the requested delivery date mentioned in excel file and it should applicable on all the line items. |
| 11 |
Upload an order sheet with same sold-to and ship-to partner into the sell sheet program and execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create with same partner number as sold-to and ship-to. |
| 12 |
Upload an order sheet with different sold-to and ship-to partner into the sell sheet program and execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create with different sold-to and ship-to partner number. |
| 13 |
Upload an order sheet with requested delivery date format (MM/DD/YYYY) into the sell sheet program and execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create with requested delivery date format MM/DD/YYYY at header and it should applicable to all the line items and schedule line level. |
| 14 |
Upload an order sheet without Cancel date into the sell sheet program and execute it. (Excel file contains 2 sheets) |
|
| 15 |
Upload an order sheet with invalid grid sizes for the material into the sell sheet program and execute it.(Excel file contains 2 sheets. Sheet1- Invalid size, Sheet2- Valid sizes) |
Sales Order/Contract should be created with rejection reason J2 for the invalid sizes (Sheet1) and Sheet2 SO should be created without J2 rejection. |
| 16 |
Upload an order sheet with materials of different master grids in the sell sheet program and execute it. (Excel file contains 2 sheets) |
Sales Order/Contract should create with materials of different master grids. |
| 17 |
Upload an order sheet as below into the sell sheet program and execute it. (Sheet1- Good data, Sheet2- Invalid material) Sheet1- Item-1 with Material number, size, and quantity. Item -2 with only size and quantity. And Sheet2- Item 1 & 2 with all required inputs. |
1 sales order/Contract should be created for Sheet2 and no sales order/Contract should be created for Sheet1 and error message should be displayed " Sheet1-Invalid material input". |
| 17A |
Excel file has 2 sheets. Where, Sheet 1 named Sheet2- should be filled with valid data, And Sheet 2 named Shee1- with Invalid material number. |
Sales order/Contract should be created for 1st Sheet i.e Sheet2 and no sales order/Contract should be created for 2nd sheet i.e Sheet1. And Error Message "Sheet1-Material 12222121211 is not defined for sales org.3200, distr.chan.10, language EN" should be displayed. |
| 18 |
Upload an order sheet with 50 items. And repeat the same item/sizes. (Excel file contains 2 Sheet, Each sheet contains 50 items.) |
Sales order should create successfully combining the same materials & sizes, both at item level and schedule line level. And Contract should create successfully combining the same materials at item level and it should not combine sizes together at schedule line level. |
| 19 |
Upload an order sheet with customer marked for deletion indicator, in the sell sheet program and execute it. (Excel file contains 2 sheets. Sheet1- With deletion indicator enabled SP, Sheet2- w/o deletion indicator SP) |
Sales Order/Contract should create and system should prompt message regarding customer marked for deletion, once user display the Sales Order/Contract. |
| 20 |
Upload an order sheet with invalid customer into the sell sheet program and execute it. (Excel file contains 2 sheets. Where, Sheet1-With Invalid Customer SP, Sheet2- With Valid customer) |
Sales order/Contract should not be created for Sheet1 and appropriate system message should be displayed. And Sales order/Contract should be created for Sheet2. |
| 21 |
Fill the order sheet with Materials with enough stock and availability check as Z2 in the MM .Upload into the sell sheet program and execute it. (Excel file contains 2 sheets.) |
Sales Order/Contract should be created and quantities should be confirmed on the requested delivery date. |
| 22 |
Fill the order sheet with Materials with no stock and availability check as Z2 in MM. Upload into the sell sheet program and execute it. (Excel file contains 2 sheets.) |
Sales Order/Contract should be created by confirming the delivery date as there is no enough stock on the requested delivery date. |
| 23 |
Fill the order sheet with Materials with enough stock and availability check as Z3. Upload into the sell sheet program and execute it. (Excel file contains 2 sheets.) |
Sales Order/Contract should be created and quantities should be confirmed on the requested delivery date. |
| 24 |
Fill the order sheet with Materials with no stock and availability check is Z3. Upload into the sell sheet program and execute it. (Excel file contains 2 sheets.) |
Sales Order/Contract should be created and material/sizes are rejected with J0 as there is no stock. |
| 25 |
Fill the order sheet with items as below with stock available on requested delivery date and upload into the sell sheet program and execute it. (Excel file contains 2 sheets. Where Item-1 - with availability check is Z2. Item-2 - with availability check is Z3) |
Sales Order/Contract should be created and quantities should be confirmed on the requested delivery date. |
| 26 |
Fill the order sheet with 2 items as below with no stock available on requested delivery date. Upload into the sell sheet program and execute it. (Excel file contains 2 sheets. Where Item-1 - with availability check is Z3 and Item-2 with availability check is Z2) |
Sales Order/Contract should be created and program should propose next possible delivery date for item-2 (Availability check is Z2) and For item-1 (Availability check is Z3) system should propose rejection reason J0. |
| 27 |
Input legacy Sold-to & Ship-to (Excel file contains 2 sheets) |
Sales Order/Contract should be created with SAP Sold-to & Ship-to and appropriate system message should be displayed. |
| 28 |
Input legacy Sold-to and SAP Ship-to; (Excel file contains 2 sheets, where Sheet1- SAP Sold-to maintained in Ztable , Sheet2- SAP Sold-to not maintained in Ztable) |
For sheet-1 Sales order/Contract should be created and for Sheet-2 Sales Order/Contract should not be created and appropriate system message should be displayed. |
| 29 |
Input legacy Ship-to and SAP Sold-to; (Excel file contains 2 sheets, where Sheet1- SAP Ship-to maintained in Ztable, Sheet2- SAP Ship-to not maintained in Ztable) |
For shee1- Sales order/Contract should be created and for Sheet2- Sales Order/Contract should not be created and appropriate system message should displayed. |
| 30 |
Input legacy Sold-to & Ship-to; (Excel file contains 2 sheets, where Sheet1- SAP Sold-to & Ship-to maintained in Ztable , Sheet2- SAP Sold-to & Ship-to not maintained in Ztable) |
For Sheet1- Sales order/Contract should be created and For Sheet2- Sales Order/Contract should not be created and appropriate system message should be displayed. |
| 31 |
Input SAP Sold-to and legacy Ship-to; (Excel file contains 2 sheets, where Sheet1- Shitp-to maintained in CMR, Sheet2- Ship-to not maintained in CMR. |
For Sheet1- Sales order/Contract should be created and for Sheet2- Sales Order/Contract should not be created and appropriate system message should be displayed. |
| 32 |
One of the tabs has junk data; Sheet 1, 2, 4 - Has good data, Sheet 3 - Has Bad data. Note: Sheet has renamed.(JSP1/2/3/4) |
For Sheet 1,2,4 sales order/Contract should be created and sheet 3 should be skipped with error message. Error sheet should display with the sheet name. |
| 33 |
Run the load file in background |
Program should not run in background |
| 34 |
Fill the order sheet - contains 3 sheets. Where. Sheet1 contains valid data and remaining tabs(Sheet2,3) are blank. And upload into the sell sheet program and execute it. |
Program should create sales order/Contract for filled sheet (Sheet1) and remaining sheet should be skipped and program should trough error message for those blank sheets. |
| 35 |
If there’s a blank tab preceded by a tab with good data and then followed by another tab with blank tab/bad data. Sheet 1-Blank, Sheet2-Good data, Sheet3-Bad data |
1 Sales order/Contract should be created for Sheet 2. not for Sheet1 & Sheet3. Error message should be display for Sheet1 & 3. |
| 36 |
Fill the order sheet with 50 sheets. And upload into the sell sheet program and execute it. This is to check the limitation and performance. |
50 Sales orders/Contracs should be created. |
Solution Details
[Provide complete technical details for configuration or programming here]
Develop a Program to create contract/ Sales order from Order Sheet. Order Sheet is an Excel File which is uploaded from desktop and one contract/ sales order is created per SHEET in the load file.
Order Sheet File:
The Order Sheet has lot of data, program should read necessary data and ignore other information,
The required Fields are,
Header Level: The header Level data is as follows.
Sales Organization = '3200',
Distribution Channel = '10',
Division = '00',
Document Type 'ZCQ'/ 'ZOR'.
| Row No |
Column |
Description |
||
| 12 |
C |
Sold To |
||
| 13 |
C |
ShipTo |
||
| 14 |
C |
Customer PO |
||
| 15 |
C |
Required Delivery Date |
||
Item Level data is as follows,
Item level data should be considered from Line/Row 19.
| Column |
Description |
|
| C |
Material |
|
| J |
Grid Value1 |
|
| K |
Quantity 1 |
|
| L |
Grid Value2 |
|
| M |
Quantity 2 |
|
| N |
Grid Value 3 |
|
| O |
Quantity 3 |
|
| P |
Grid Value 4 |
|
| Q |
Quantity 4 |
|
| R |
Grid Value 5 |
|
| S |
Quantity 5 |
|
| T |
Grid Value 6 |
|
| U |
Quantity 6 |
|
| V |
Grid Value 7 |
|
| " |
" |
Lines with no quantity should be Ignored.
Invalid Materials should be captured and displayed in Log and Contract/Sales order should not be created.
- A new program ' ZSD_JPN_ORD_SHT_MULTIPLE' has been developed to create Contract/Sales order from load file with multiple sheets.
- The program can handle both Contract and Sales order creation.
- Transaction code ZJCQ has been assigned with the program.
- The program can handle any number of sheets from the Upload file. i.e Excel file.
- The program can read any custom name of sheets available in load file.
- Error log has been updated to display the error sheet name with the actual sheet names from the load file.
- The program can read data from the protected workbook/sheet.
- The program cumulates the sizes, if it is available multiple times in the load file.
Issues
[List Issues / Bugs identified in configuration or development]
NA